import pandas as pd
import numpy as np


def generate_sql_from_excel(excel_path, book_name_mapping=None, output_sql_path=None):
    """
    从Excel文件生成SQL插入语句

    参数:
    excel_path: Excel文件路径
    book_name_mapping: book_id到book_name的映射字典
    output_sql_path: 输出SQL文件路径（可选）
    """

    try:
        # 读取Excel文件
        df = pd.read_excel(excel_path)

        # 检查必需的列是否存在
        required_columns = [
            'resource_id', 'book_id', 'book_sort', 'content_id', 'content_name',
            'content_sort', 'lesson_id', 'lesson_name', 'content_type', 'user_type'
        ]

        missing_columns = [col for col in required_columns if col not in df.columns]
        if missing_columns:
            raise ValueError(f"Excel文件缺少以下必需列: {missing_columns}")

        # 如果提供了book_name映射，使用映射；否则使用模块名称或默认值
        if book_name_mapping:
            df['book_name'] = df['book_id'].map(book_name_mapping)
            # 对于没有映射的book_id，使用默认名称
            df['book_name'] = df['book_name'].fillna('未知模块')
        elif '模块名称' in df.columns:
            # 如果Excel中有模块名称列，使用它作为book_name
            df['book_name'] = df['模块名称']
        else:
            # 否则使用默认值
            df['book_name'] = '默认模块'

        # 添加lesson_sort列（默认为0）
        if 'lesson_sort' not in df.columns:
            df['lesson_sort'] = 0

        # 生成SQL语句列表
        sql_statements = []

        # SQL模板
        sql_template = """INSERT INTO `holiday_content_resource_detail_v2`(
    `resource_id`, `book_id`, `book_name`, `book_sort`, `content_id`, `content_name`, 
    `content_sort`, `lesson_id`, `lesson_name`, `lesson_sort`, `content_type`, `duration`, `user_type`
) VALUES ({resource_id}, {book_id}, '{book_name}', {book_sort}, {content_id}, '{content_name}', {content_sort}, {lesson_id}, '{lesson_name}', {lesson_sort}, {content_type}, {duration}, {user_type});"""

        # 处理每一行数据
        for index, row in df.iterrows():
            # 处理可能的空值和特殊字符
            def safe_str(value):
                if pd.isna(value) or value == '':
                    return ''
                # 转义SQL中的单引号
                return str(value).replace("'", "''")

            def safe_int(value, default=0):
                if pd.isna(value):
                    return default
                try:
                    return int(value)
                except (ValueError, TypeError):
                    return default

            # 准备SQL参数
            sql_params = {
                'resource_id': safe_int(row['resource_id']),
                'book_id': safe_int(row['book_id']),
                'book_name': safe_str(row['book_name']),
                'book_sort': safe_int(row['book_sort']),
                'content_id': safe_int(row['content_id']),
                'content_name': safe_str(row['content_name']),
                'content_sort': safe_int(row['content_sort']),
                'lesson_id': safe_int(row['lesson_id']),
                'lesson_name': safe_str(row['lesson_name']),
                'lesson_sort': safe_int(row.get('lesson_sort', 0)),
                'content_type': safe_int(row['content_type']),
                'duration': 0,  # 默认为0
                'user_type': safe_int(row['user_type'])
            }

            # 生成SQL语句
            sql_statement = sql_template.format(**sql_params)
            sql_statements.append(sql_statement)

        # 合并所有SQL语句
        full_sql = '\n\n'.join(sql_statements)

        # 如果指定了输出路径，保存到文件
        if output_sql_path:
            with open(output_sql_path, 'w', encoding='utf-8') as f:
                f.write("-- 自动生成的SQL插入语句\n")
                f.write(f"-- 生成时间: {pd.Timestamp.now().strftime('%Y-%m-%d %H:%M:%S')}\n")
                f.write(f"-- 总计 {len(sql_statements)} 条记录\n\n")
                f.write(full_sql)
            print(f"✅ SQL语句已保存到: {output_sql_path}")

        # 显示统计信息
        print(f"\n=== SQL生成统计 ===")
        print(f"成功生成 {len(sql_statements)} 条SQL插入语句")

        # 统计各字段分布
        print(f"\nresource_id分布: {dict(df['resource_id'].value_counts().sort_index())}")
        print(f"content_type分布: {dict(df['content_type'].value_counts().sort_index())}")
        print(f"book_id范围: {df['book_id'].min()} - {df['book_id'].max()}")

        # 显示前3条SQL语句预览
        print(f"\n=== 前3条SQL语句预览 ===")
        for i, sql in enumerate(sql_statements[:3]):
            print(f"\n-- 第{i + 1}条:")
            print(sql)

        if len(sql_statements) > 3:
            print(f"\n... 还有 {len(sql_statements) - 3} 条语句")

        return full_sql

    except FileNotFoundError:
        print(f"❌ 错误: 找不到Excel文件 '{excel_path}'")
        return None
    except Exception as e:
        print(f"❌ 生成SQL时出现错误: {e}")
        import traceback
        traceback.print_exc()
        return None


def create_book_name_mapping_from_excel(excel_path):
    """
    从Excel文件中自动创建book_id到book_name的映射
    """
    try:
        df = pd.read_excel(excel_path)

        if 'book_id' in df.columns and '模块名称' in df.columns:
            # 创建book_id到模块名称的映射
            mapping = df[['book_id', '模块名称']].drop_duplicates().set_index('book_id')['模块名称'].to_dict()
            print(f"✅ 自动创建book_name映射: {mapping}")
            return mapping
        else:
            print("❌ 无法自动创建映射：缺少 'book_id' 或 '模块名称' 列")
            return None

    except Exception as e:
        print(f"❌ 创建映射时出错: {e}")
        return None


def batch_generate_sql(excel_path, output_sql_path='holiday_content_insert.sql', custom_book_mapping=None):
    """
    批量生成SQL的完整流程
    """

    print("=== 开始生成SQL插入语句 ===")

    # 1. 自动创建book_name映射（如果没有提供自定义映射）
    if custom_book_mapping is None:
        print("步骤1: 尝试自动创建book_name映射...")
        book_mapping = create_book_name_mapping_from_excel(excel_path)
    else:
        print("步骤1: 使用自定义book_name映射...")
        book_mapping = custom_book_mapping

    # 2. 生成SQL语句
    print("步骤2: 生成SQL插入语句...")
    sql_content = generate_sql_from_excel(excel_path, book_mapping, output_sql_path)

    if sql_content:
        print(f"\n🎉 SQL生成完成！")
        print(f"📁 文件保存位置: {output_sql_path}")

        # 显示文件大小信息
        import os
        if os.path.exists(output_sql_path):
            file_size = os.path.getsize(output_sql_path)
            print(f"📊 文件大小: {file_size:,} 字节")

        return True
    else:
        print("❌ SQL生成失败")
        return False


# 使用示例
def main(excel_path):
    """
    主函数 - 使用示例
    """

    # 方式1: 简单使用（自动创建book_name映射）
    batch_generate_sql(excel_path)

    # 方式2: 使用自定义book_name映射
    # custom_mapping = {
    #     1: '动力激发',
    #     2: '学习方法',
    #     3: '知识梳理',
    #     4: '能力提升'
    # }
    # batch_generate_sql(excel_path, 'custom_insert.sql', custom_mapping)


def check_excel_for_sql_generation(excel_path):
    """
    检查Excel文件是否包含生成SQL所需的所有列
    """
    try:
        df = pd.read_excel(excel_path)

        required_columns = [
            'resource_id', 'book_id', 'book_sort', 'content_id', 'content_name',
            'content_sort', 'lesson_id', 'lesson_name', 'content_type', 'user_type', 'lesson_sort'
        ]

        print(f"=== Excel文件检查 ===")
        print(f"文件: {excel_path}")
        print(f"总行数: {len(df)}")
        print(f"总列数: {len(df.columns)}")

        print(f"\n必需列检查:")
        missing_cols = []
        for col in required_columns:
            if col in df.columns:
                print(f"✅ {col}")
            else:
                print(f"❌ {col} (缺失)")
                missing_cols.append(col)

        optional_cols = ['模块名称', '阶段', '资源类型', '资源名称']
        print(f"\n可选列检查:")
        for col in optional_cols:
            if col in df.columns:
                print(f"✅ {col}")
            else:
                print(f"➖ {col} (不存在)")

        if missing_cols:
            print(f"\n❌ 无法生成SQL: 缺少必需列 {missing_cols}")
            return False
        else:
            print(f"\n✅ Excel文件检查通过，可以生成SQL")
            return True

    except Exception as e:
        print(f"❌ 检查Excel文件时出错: {e}")
        return False


if __name__ == "__main__":
    # 检查Excel文件（可选）
    excel_file = 'D:\\pycharmProject\\pythonProject\\xinli_data_inti\\processed_data_with_all_columns_v2.xlsx'

    if check_excel_for_sql_generation(excel_file):
        # 运行主程序
        main(excel_file)
    else:
        print("请先处理Excel文件，确保包含所有必需的列")